<html>

<head>
    <script src="dist/xlsx.bundle.js"></script>
    <style type="text/css">
        .tbexport {
            border-collapse: collapse;
            width: 500px;
        }

        .tbexport th,
        td {
            border: 1px solid #ddd;
            padding: 8px;
            text-align: left;
        }

        .tbexport2 {
            border-collapse: collapse;
            width: 100%;
        }

        .tbexport2 th,
        td {
            border: 1px solid #ddd;

            text-align: left;
        }
    </style>
    <script src="https://code.jquery.com/jquery-3.7.1.min.js"
        integrity="sha256-/JqT3SQfawRcv/BIHPThkBvs0OEvtFFmqPF/lYI/Cxo=" crossorigin="anonymous"></script>
    <!--    如果这个jquery连接不上，可以自己下载一个-->

    <head>

    <body>
        <table id="TableToExport" class="tbexport">
            <tr>
                <td>序号</td>
                <td>名称</td>
                <td>测试1</td>
                <td>测试2</td>
                <td>测试3</td>
                <td>测试4</td>
            </tr>
            <tr>
                <td>1</td>
                <td>bb</td>
                <td>345</td>
                <td>566</td>
                <td>777</td>
                <td>888</td>
            </tr>
            <tr>
                <td>2</td>
                <td>bb</td>
                <td rowspan="3">999</td>
                <td>566</td>
                <td>777</td>
                <td>888</td>
            </tr>
            <tr>
                <td>3</td>
                <td>bb</td>
                <td>999</td>
                <td>1010</td>
                <td>111</td>
            </tr>

            <tr>
                <td>4</td>
                <td>bb</td>
                <td>333</td>
                <td>44</td>
                <td>55</td>
            </tr>
            <tr>
                <td colSpan="2">合计</td>
                <td>22</td>
                <td>77</td>
                <td>88</td>
                <td>99</td>
            </tr>
        </table>


        <style type="text/css">
            .datatable {}

            .datatable td {
                padding: 10px;
                border: 1px solid #000
            }

            .datatable thead td {
                background-color: #f0f0f0
            }
        </style>

        <button id="sheetjsexport" onclick="myExportExcel()"><b>合并单元格导出带边框</b></button>

        <button onclick="myExportExcel2()"><b>导出带表头表格</b></button>
        <script>

            var MyStyle = {
                borderStyle: {
                    border: {
                        top: {
                            style: 'thin',
                            color: {
                                auto: 1
                            }
                        },
                        left: {
                            style: 'thin',
                            color: {
                                auto: 1
                            }
                        },
                        right: {
                            style: 'thin',
                            color: {
                                auto: 1
                            }
                        },
                        bottom: {
                            style: 'thin',
                            color: {
                                auto: 1
                            }
                        }
                    },
                },
                centerStyle: {
                    alignment: {
                        horizontal: "center",
                        vertical: "center"
                    }
                },
                leftStyle: {
                    alignment: {
                        horizontal: "left",
                        vertical: "center"
                    },
                },
                rightStyle: {
                    alignment: {
                        horizontal: "right",
                        vertical: "center"
                    },
                },
                boldStyle: { //设置一级标题样式
                    font: {
                        // sz: 12,
                        bold: true,
                        // color: {rgb: "000000"}
                    }
                },
                smallStyle: { //设置一级标题样式
                    font: {
                        sz: 9,
                        bold: false,
                        color: { rgb: "222222" }
                    }
                },
                titleStyle: { //设置一级标题样式
                    font: {
                        sz: 14,
                        bold: true,
                        // color: {rgb: "000000"}
                    },
                    alignment: {
                        horizontal: "center",
                        vertical: "center"
                    },
                },
                title2Style: { //设置二级标题样式
                    font: {
                        bold: true,
                        // color: {rgb: "000000"}
                    },
                    alignment: {
                        horizontal: "center",
                        vertical: "center"
                    },
                },
                bgStyle: {
                    fill: {
                        fgColor: { rgb: "EEECE1" }
                    },
                }
            };

            function checkEmptyItem(ws) {

                var range = XLSX.utils.decode_range(ws['!ref']);
                // // 计算最大列号
                var maxCol = range.e.c; // 'e' 代表结束列，'c' 是列号的字段
                var maxRow = range.e.r;
                //不加时，合并单元格边框会缺少
                for (let i = 0; i <= maxRow; i++) {
                    for (let j = 0; j <= maxCol; j++) {

                        if (ws[XLSX.utils.encode_cell({ r: i, c: j })] == undefined) {
                            ws[XLSX.utils.encode_cell({ r: i, c: j })] =
                                { t: 's', v: "", z: XLSX.utils.encode_cell({ r: i, c: j }) };
                        }
                    }
                }
                return ws;
            }




            function myExportExcel() {
                /* Create worksheet from HTML DOM TABLE */
                var wb = XLSX.utils.table_to_book(document.getElementById("TableToExport"), { sheet: '测试', raw: true })
                /* Export to file (start a download) */
                var ws = wb.Sheets["测试"]; //  get the current
                const range = XLSX.utils.decode_range(ws['!ref']);
                // // 计算最大列号
                const maxCol = range.e.c; // 'e' 代表结束列，'c' 是列号的字段
                const maxRow = range.e.r;

                let arr = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P"] //总共多少列

                //设置公共样式
                ws = checkEmptyItem(ws);
                for (let i = 1; i <= maxRow + 1; i++) {
                    for (j = 0; j <= maxCol; j++) {
                        var item = arr[j];
                        let str = item + i;

                        if (ws[str]) {
                            var cellStyle = {};
                            cellStyle = $.extend(cellStyle, MyStyle.borderStyle);
                            cellStyle = $.extend(cellStyle, MyStyle.centerStyle);
                            if (i == 1) {
                                cellStyle = $.extend(cellStyle, MyStyle.title2Style);
                                ws['!cols'][j] = { wpx: 120 };//设置列的高度
                            }
                        }
                        Object.assign(ws[str], { s: cellStyle });
                    }
                    ws['!rows'][i - 1] = { hpx: 30 };//设置行的高度
                }
                XLSX.writeFile(wb, "SheetJSTable.xlsx");
            }
            //导出带表头样式
            function myExportExcel2() {
                /* Create worksheet from HTML DOM TABLE */
                var wb = XLSX.utils.table_to_book(document.getElementById("TableToExport"), {
                    sheet: '测试',
                    raw: true,
                    origin: { c: 0, r: 1 }
                }); //从第1列，第三行开始
                var ws = wb.Sheets["测试"]; //  get the current

                // 要插入的新行数据
                const newRow = ['测试完成情况'];
                //添加1行合并单元格内容
                var nMergeLength = ws["!merges"].length;
                ws["!merges"][nMergeLength] = XLSX.utils.decode_range("A1:F1");
                // 表头设置值
                ws[XLSX.utils.encode_cell({ r: 0, c: 0 })] =
                    { t: 's', v: newRow[0], z: XLSX.utils.encode_cell({ r: 0, c: 0 }) };
                //设置表头样式
                Object.assign(ws["A1"], { s: MyStyle.titleStyle });
                ws['!rows'][0] = { hpx: 50 };

                const range = XLSX.utils.decode_range(ws['!ref']);
                // // 计算最大列号
                const maxCol = range.e.c; // 'e' 代表结束列，'c' 是列号的字段
                const maxRow = range.e.r;
                let arr = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P"] //总共多少列


                ws = checkEmptyItem(ws);
                //设置公共样式
                for (let i = 2; i <= maxRow + 1; i++) {
                    for (j = 0; j <= maxCol; j++) {
                        var item = arr[j];
                        let str = item + i;

                        if (ws[str]) {
                            var cellStyle = {};
                            cellStyle = $.extend(cellStyle, MyStyle.borderStyle);
                            cellStyle = $.extend(cellStyle, MyStyle.centerStyle);
                            if (i == 2) {
                                cellStyle = $.extend(cellStyle, MyStyle.title2Style, MyStyle.bgStyle);
                                ws['!cols'][j] = { wpx: 120 };//设置列的高度
                            }
                        }
                        Object.assign(ws[str], { s: cellStyle });
                    }
                    ws['!rows'][i - 1] = { hpx: 30 };//设置行的高度
                }
                XLSX.writeFile(wb, "SheetJSTable.xlsx");
            }
        </script>
        ===================================================


    </body>

</html>